﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DALHelper.Business;

namespace DALHelper.DAL
{
    public class RoomDAL
    {
        private SqlConnection m_connection;

        public RoomDAL(SqlConnection conn)
        {
            m_connection = conn;
        }

        public IEnumerable<RoomCategory> GetRoomFree(DateTime dtStart, DateTime dtEnd)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_TraCuuPhongTrong", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter para;

            para = new SqlParameter("BatDau", System.Data.SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = dtStart;
            cmd.Parameters.Add(para);

            para = new SqlParameter("KetThuc", System.Data.SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = dtEnd;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            SqlDataReader result = null;
            try
            {
                result = cmd.ExecuteReader();

                List<RoomCategory> lst = new List<RoomCategory>();
                while (result.Read())
                {
                    RoomCategory r = new RoomCategory();
                    r.RoomCatID = (string)result["MA_LOAI"];
                    r.RoomForm = (string)result["HINH_THUC_PHONG"];
                    r.RoomLevel = (string)result["HANG_PHONG"];
                    r.NumOfBeds = (int)result["SO_GIUONG"];
                    r.RoomPrice = (decimal)result["DON_GIA"];
                    r.AvailCount = (int)result["SO_LUONG"];
                    lst.Add(r);
                }

                return lst;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }

        public void CheckIn(long lTransID)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_NhanPhong", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter para;

            string id = "000" + lTransID;
            id = "GD" + id.Substring(id.Length - 3);
            para = new SqlParameter("MaDoan", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = id;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }

        public void CheckOut(long lTransID)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_TraPhong", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter para;

            string id = "000" + lTransID;
            id = "GD" + id.Substring(id.Length - 3);
            para = new SqlParameter("MaDoan", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = id;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }

        public decimal Bill(long lTransID)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_TinhTienPhong", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter para;

            string id = "000" + lTransID;
            id = "GD" + id.Substring(id.Length - 3);
            para = new SqlParameter("MaDoan", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = id;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 1;
            cmd.Parameters.Add(para);


            para = new SqlParameter("TongTien", System.Data.SqlDbType.Money);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            try
            {
                cmd.ExecuteNonQuery();
                return (decimal)cmd.Parameters["TongTien"].Value;
            }
            catch (SqlException)
            {
                return 0;
            }
            finally
            {
                m_connection.Close();
            }
        }

        public void Compensation(string TransID, string sDetail)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_CapNhatTienBoiThuong", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter para;

            para = new SqlParameter("MaDoan", System.Data.SqlDbType.VarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = TransID;
            cmd.Parameters.Add(para);

            para = new SqlParameter("ChuoiChiTiet", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = sDetail;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }
    }
}
